﻿namespace WMS6.SQLServerDAL
{
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Text;
    using WMS6.DataEntity;
    using WMS6.DBUtility;
    using WMS6.IDAL;
    using WMS6.QueryEntity;

    public class CustomizeReportDA : ICustomizeReportDA
    {
        public DataSet GetNSNPerformanceReportByDataSet(List<string> sqlWhere, NSNPerformanceReportQueryEntity nsnPerformanceReportQueryEntity, PubParasInfo pubParasInfo, string whLoginID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(" SELECT R.VENDOR_CONTACT AS R_VENDOR_CONTACT ,R.VENDOR_CITY AS R_VENDOR_CITY,VR.COMPANY AS R_VENDOR_ID ,VR.DESCRIPTION AS R_VENDOR_NAME,UM.USER_NAME AS R_CREATED_BY,R.VENDOR_PHONE AS R_VENDOR_PHONE,R.UDF3 AS R_UDF3,");
            strSql.Append(" R.RECEIPT_ID AS R_RECEIPT_ID ,RD.SKU_ID AS RD_SKU_ID ,S.DESCR AS RD_DESCR, L.LOT_ATTR02 AS R_LOT_ATTR02 ,L.LOT_ATTR01 AS R_LOT_ATTR01 ,L.EXTERNAL_LOT AS R_EXTERNAL_LOT,L.LOT_ATTR03 AS R_RAM, ");
            strSql.Append(" OD.SKU_ID AS OD_SKU_ID,L1.LOT_ATTR02 AS OD_LOT_ATTR02,L1.LOT_ATTR01 AS OD_LOT_ATTR01 ,L1.EXTERNAL_LOT AS OD_EXTERNAL_LOT,");
            strSql.Append(" MD.SKU_ID AS MD_SKU_ID,L2.LOT_ATTR02 AS MD_LOT_ATTR02, CASE  WHEN INVT.LOT_ATTR01 IS NULL THEN 'NSN' ELSE INVT.LOT_ATTR01 END AS MD_LOT_ATTR01,L2.EXTERNAL_LOT AS MD_EXTERNAL_LOT, CASE L2.LOT_ATTR01 WHEN 'NSN' THEN 'MSPS' ELSE 'SWAP'END AS SAP ,");
            strSql.Append(" M.UDF1,M.UDF3 AS M_UDF3,M.REMARK AS M_REMARK,M.UDF9 AS M_UDF9,S.SKU_CLASS_ID,M.MOVEMENT_DATE AS M_MOVEMENT_DATE,RD.RECEIPT_DATE AS R_RECEIPT_DATE,M.APPROVED_DATE AS M_APPROVED_DATE,M.UDF10,M.UDF2,R.UDF1 AS R_UDF1,R.UDF2 AS R_UDF2,R.REMARK AS  R_REMARK, CONVERT(DECIMAL(22,2),CONVERT(DECIMAL,DATEDIFF(n,M.MOVEMENT_DATE , M.UDF10 ))/CONVERT(DECIMAL,59) ) AS M_HOUR,MD.REMARK AS MD_REMARK");
            strSql.Append(" FROM  @_@RECEIPT R LEFT JOIN @_@VENDOR VR ON R.VENDOR_ID=VR.VENDOR_ID INNER JOIN @_@RECEIPT_DETAIL RD ON R.RECEIPT_ID = RD.RECEIPT_ID AND RD.STATUS='ReceivedComplete' INNER JOIN  @_@SKU S ON S.SKU_ID= RD.SKU_ID AND S.OWNER_ID=RD.OWNER_ID ");
            strSql.Append(" INNER JOIN  @_@INV_TRANS IT ON  RD.RECEIPT_ID = IT.RECEIPT_ID AND RD.RECEIPT_LINE_ID= IT.RECEIPT_LINE_ID AND IT. TRANS_TYPE='IN' AND IT.STATUS ='OK' ");
            strSql.Append(" INNER JOIN @_@LOT_ATTRIBUTE L ON IT.LOT = L.LOT AND L.LOT_ATTR09='故障件/DAMAGED'  LEFT JOIN @_@ORDER_DETAIL OD ON OD.ORDER_ID=RD.RECEIPT_ID AND OD.LINE_ID=RD.RECEIPT_LINE_ID AND OD.STATUS='Shipped' LEFT JOIN @_@PICK_DETAIL PD ON OD.ORDER_ID =PD.ORDER_ID ");
            strSql.Append(" LEFT JOIN @_@LOT_ATTRIBUTE L1 ON PD.LOT = L1.LOT  LEFT JOIN @_@MOVEMENT_DETAIL MD ON RD.RECEIPT_ID = MD.MOVEMENT_ID AND MD.LINE_ID=RD.RECEIPT_LINE_ID AND MD.STATUS='Complete' ");
            strSql.Append(" LEFT JOIN @_@LOT_ATTRIBUTE L2 ON MD.LOT = L2.LOT LEFT JOIN @_@MOVEMENT M  ON MD.MOVEMENT_ID = M.MOVEMENT_ID ");
            strSql.Append(" LEFT JOIN @_@INV_TRANS INVT ON L2.EXTERNAL_LOT = INVT.EXTERNAL_LOT  AND  INVT.EXTERNAL_LOT !='NONE' AND INVT.TRANS_TYPE='IN' AND INVT.SOURCE_TRANS_TYPE='ReceiptDetailAdd' AND INVT.STATUS='OK' ");
            strSql.Append(" INNER JOIN sysadm.USER_OWNER UO ON RD.OWNER_ID = UO.OWNER_ID AND UO.USER_ID = @USER_ID ");
            strSql.Append(" INNER JOIN SYSADM.USER_MST UM ON R.CREATED_BY= UM.USER_ID");
            string sql = strSql.ToString().Replace("@_@", whLoginID).Replace("@USER_ID", "'" + pubParasInfo.UserID + "'");
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                if (i == 0)
                {
                    sql = sql + " WHERE " + sqlWhere[i].ToString();
                }
                else
                {
                    sql = sql + " AND " + sqlWhere[i].ToString();
                }
            }
            if (!nsnPerformanceReportQueryEntity.IsGetAll)
            {
                sql = PagingHelper.GetPagingSQL(sql, nsnPerformanceReportQueryEntity.CurrentPage, nsnPerformanceReportQueryEntity.PageSize, nsnPerformanceReportQueryEntity.SortField, nsnPerformanceReportQueryEntity.SortDirection);
            }
            else
            {
                sql = sql + " ORDER BY " + nsnPerformanceReportQueryEntity.SortField + " " + nsnPerformanceReportQueryEntity.SortDirection;
            }
            return DBHelper.ExecuteDataSet(CommandType.Text, sql);
        }

        public DataSet GetTNTDailyInventoryRptDataSet(List<string> sqlWhere, InventoryQueryEntity inventoryQueryEntity, PubParasInfo pubParasInfo, string whLoginID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(" SELECT * FROM (");
            strSql.Append(" SELECT S.SKU_ID,S.DESCR AS SKU_DESCR,S.DESCR2 AS SKU_DESCR2,S.SKU_GROUP,SC.SKU_CLASS_ID,SC.DESCR AS SKU_CLASS_NAME,LA.EXPIRY_DATE,INV.UNIT_PRICE,O.OWNER_ID,O.COMPANY AS OWNER_NAME, ");
            strSql.Append("   DATEPART(YEAR,LA.EXPIRY_DATE) AS EXPIRY_YEAR,DATEPART(MONTH,LA.EXPIRY_DATE) AS EXPIRY_MONTH,DATEPART(DAY,LA.EXPIRY_DATE) AS EXPIRY_DAY, ");
            strSql.Append("   SUM(INV.QTY) AS QTY ");
            strSql.Append(" FROM @_@INV_LOT INV ");
            strSql.Append("   LEFT JOIN @_@SKU S ON S.SKU_ID = INV.SKU_ID AND S.OWNER_ID = INV.OWNER_ID ");
            strSql.Append("   LEFT JOIN @_@SKU_CLASS SC ON SC.SKU_CLASS_ID = S.SKU_CLASS_ID ");
            strSql.Append("   LEFT JOIN @_@LOT_ATTRIBUTE LA ON LA.LOT = INV.LOT ");
            strSql.Append("   LEFT JOIN @_@[OWNER] O ON O.OWNER_ID = S.OWNER_ID ");
            strSql.Append("   INNER JOIN sysadm.USER_OWNER UO ON O.OWNER_ID = UO.OWNER_ID AND UO.USER_ID = @USER_ID ");
            if (!string.IsNullOrEmpty(inventoryQueryEntity.QTY))
            {
                strSql.Append(" WHERE INV.QTY " + inventoryQueryEntity.QTY);
            }
            strSql.Append(" GROUP BY S.SKU_ID,S.DESCR,S.DESCR2,S.SKU_GROUP,SC.SKU_CLASS_ID,SC.DESCR,LA.EXPIRY_DATE,INV.UNIT_PRICE,O.OWNER_ID,O.COMPANY ) tmp WHERE 1=1 ");
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                strSql.Append(" AND " + sqlWhere[i].ToString());
            }
            string sql = string.Empty;
            sql = strSql.ToString().Replace("@_@", whLoginID).Replace("@USER_ID", "'" + pubParasInfo.UserID + "'");
            if (!inventoryQueryEntity.IsGetAll)
            {
                sql = PagingHelper.GetPagingSQL(sql, inventoryQueryEntity.CurrentPage, inventoryQueryEntity.PageSize, inventoryQueryEntity.SortField, inventoryQueryEntity.SortDirection);
            }
            else
            {
                sql = sql + " ORDER BY " + inventoryQueryEntity.SortField + " " + inventoryQueryEntity.SortDirection;
            }
            return DBHelper.ExecuteDataSet(CommandType.Text, sql);
        }

        public DataSet GetTNTInOutStockInvRptDataSet(List<string> sqlWhere, TNTInOutStockInvRptQueryEntity tntInOutStockInvRptQueryEntity, PubParasInfo pubParasInfo, string whLoginID)
        {
            StringBuilder StrBeginQtySql = new StringBuilder();
            StringBuilder StrInvTransWhereSql = new StringBuilder();
            StrBeginQtySql.Append("( SELECT  LOT,SUM(CASE TRANS_TYPE+SOURCE_TRANS_TYPE+STATUS  WHEN 'INRECEIPTDETAILADDOK' THEN QTY ELSE 0 END)+SUM(CASE TRANS_TYPE  WHEN 'OT' THEN QTY ELSE 0 END) AS BEGINQTY  FROM @_@INV_TRANS WHERE ");
            StrBeginQtySql.AppendFormat("  CREATED_DATE <= '{0}' ", tntInOutStockInvRptQueryEntity.START_DATETIME);
            StrInvTransWhereSql.AppendFormat(" WHERE IT.CREATED_DATE >='{0}'", tntInOutStockInvRptQueryEntity.START_DATETIME);
            if (!string.IsNullOrEmpty(tntInOutStockInvRptQueryEntity.END_DATETIME))
            {
                StrInvTransWhereSql.AppendFormat(" AND IT.CREATED_DATE <='{0}'", tntInOutStockInvRptQueryEntity.END_DATETIME);
            }
            if (!string.IsNullOrEmpty(tntInOutStockInvRptQueryEntity.OWNER_ID))
            {
                StrBeginQtySql.AppendFormat(" AND OWNER_ID {0} ", tntInOutStockInvRptQueryEntity.OWNER_ID);
            }
            if (!string.IsNullOrEmpty(tntInOutStockInvRptQueryEntity.SKU_ID))
            {
                StrBeginQtySql.AppendFormat(" AND SKU_ID  {0} ", tntInOutStockInvRptQueryEntity.SKU_ID);
            }
            StrBeginQtySql.Append("  GROUP BY LOT ) AS G  ");
            StringBuilder strSql = new StringBuilder();
            strSql.Append(" SELECT  A.SKU_ID as SKU_ID,A.OWNER_ID as OWNER_ID,S.SKU_CLASS_ID as SKU_CLASS_ID,S.SKU_GROUP as SKU_GROUP,S.DESCR AS SKU_DESCR,ISNULL(SUM(G.BEGINQTY),0) AS BEGINQTY ,ISNULL(SUM(INQTY),0) AS InBound_Qty ,ISNULL(SUM(EXPIRED_QTY),0) AS EXPIRED_QTY ,ISNULL(SUM(SCRAP_QTY),0) AS SCRAP_QTY ,ISNULL(SUM(OTQTY),0) AS OutBound_Qty ,ISNULL(SUM(BEGINQTY),0)+ ISNULL(SUM(INQTY),0)+ISNULL(SUM(EXPIRED_QTY),0)+ISNULL(SUM(SCRAP_QTY),0)+ISNULL(SUM(OTQTY),0)-ISNULL(SUM(A.QTY),0) AS OPEN_QTY,ISNULL(SUM(A.QTY),0) INV_QTY  ");
            strSql.AppendFormat(" FROM @_@INV_LOT A  LEFT JOIN  {0}   ON A.LOT =g.LOT ", StrBeginQtySql.ToString());
            strSql.Append(" LEFT JOIN ( SELECT LOT,");
            strSql.Append(" SUM(CASE IT.TRANS_TYPE+IT.SOURCE_TRANS_TYPE+IT.STATUS  WHEN 'INRECEIPTDETAILADDOK' THEN IT.QTY ELSE 0 END) AS 'INQTY',SUM(CASE  IT.TRANS_TYPE+IT.SOURCE_TRANS_TYPE+IT.STATUS WHEN 'OTPICKINGUPDATEOK' THEN IT.QTY ELSE 0 END) AS 'OTQTY'  FROM  @_@INV_TRANS IT ");
            strSql.AppendFormat(" {0} GROUP BY IT.LOT) B ON A.LOT=B.LOT ", StrInvTransWhereSql.ToString());
            strSql.Append(" LEFT JOIN (SELECT IT.LOT, SUM(CASE AD.REASON_CODE  WHEN 'EXPIRED' THEN  AD.QTY ELSE 0 END) AS 'EXPIRED_QTY', SUM(CASE AD.REASON_CODE  WHEN 'SCRAP' THEN  AD.QTY ELSE 0 END) AS 'SCRAP_QTY' ");
            strSql.Append("  FROM @_@INV_TRANS  IT INNER JOIN @_@ADJUSTMENT_DETAIL AD ON IT.DOC_ID=AD.ADJUSTMENT_ID AND IT.DOC_LINE_ID = AD.LINE_ID AND IT.TRANS_TYPE = 'AJ' AND IT.SOURCE_TRANS_TYPE='ADJUSTMENTDETAILADD' GROUP BY IT.LOT ) C ON A.LOT = C.LOT");
            strSql.Append("  LEFT JOIN  @_@SKU S ON A.SKU_ID = S.SKU_ID AND A.OWNER_ID = S.OWNER_ID ");
            strSql.Append("   INNER JOIN sysadm.USER_OWNER UO ON A.OWNER_ID = UO.OWNER_ID AND UO.USER_ID = @USER_ID ");
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                strSql.Append(" AND " + sqlWhere[i].ToString());
            }
            strSql.Append(" GROUP BY  A.SKU_ID,A.OWNER_ID,S.SKU_CLASS_ID,S.SKU_GROUP,S.DESCR ");
            string sql = string.Empty;
            sql = strSql.ToString().Replace("@_@", whLoginID).Replace("@USER_ID", "'" + pubParasInfo.UserID + "'");
            if (!tntInOutStockInvRptQueryEntity.IsGetAll)
            {
                sql = PagingHelper.GetPagingSQL(sql, tntInOutStockInvRptQueryEntity.CurrentPage, tntInOutStockInvRptQueryEntity.PageSize, tntInOutStockInvRptQueryEntity.SortField, tntInOutStockInvRptQueryEntity.SortDirection);
            }
            else
            {
                sql = sql + " ORDER BY " + tntInOutStockInvRptQueryEntity.SortField + " " + tntInOutStockInvRptQueryEntity.SortDirection;
            }
            return DBHelper.ExecuteDataSet(CommandType.Text, sql);
        }
    }
}

